﻿// ------------------------------------------------------------------------
// 项目名称：Canroc.Net 
// 版权归属：Canroc（https://gitee.com/canroc）
//
// 许可证信息
// Canroc.Net项目主要遵循 Apache 许可证（版本 2.0）进行分发和使用。许可证位于源代码树根目录中的 LICENSE-APACHE 文件。
//
// 使用条款：
// 1.使用本项目应遵守相关法律法规和许可证的要求。
// 2.不得利用本项目从事危害国家安全、扰乱社会秩序、侵犯他人合法权益等法律法规禁止的活动。
// 3.任何基于本项目二次开发而产生的一切法律纠纷和责任，我们不承担任何责任
//
// 免责声明
// 对于因使用本代码而产生的任何直接、间接、偶然、特殊或后果性损害，我们不承担任何责任。
//
// 其他重要信息
// Canroc.Net 项目的版权、商标、专利和其他相关权利均受相应法律法规的保护。
// ------------------------------------------------------------------------

using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Data;
using System.Reflection;
using System.Text.RegularExpressions;

namespace Canroc.Net.Core.Helper;

/// <summary>
///     NPOI帮助类
/// </summary>
public static partial class NpoiHelper
{
    [GeneratedRegex(@"\{\{|\}\}")]
    private static partial Regex TemplateCellOccupyRegex();

    /// <summary>
    ///     读取Excel文件到DataTable
    /// </summary>
    /// <param name="stream">文件流</param>
    /// <returns></returns>
    public static DataTable QueryAsDataTable(Stream stream)
    {
        var workbook = new XSSFWorkbook(stream);
        var sheet = workbook.GetSheetAt(0);
        var dt = new DataTable();
        var headerRow = sheet.GetRow(0);
        var cellCount = headerRow.LastCellNum;
        // 构建表头
        for (var i = headerRow.FirstCellNum; i < cellCount; i++)
        {
            var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
            dt.Columns.Add(column);
        }

        // 填充表格数据
        for (var i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
        {
            var row = sheet.GetRow(i);
            var dataRow = dt.NewRow();
            if (row == null) continue;
            for (var j = row.FirstCellNum; j < cellCount; j++)
            {
                if (row.GetCell(j) != null)
                    dataRow[j] = row.GetCell(j).ToString();
            }

            dt.Rows.Add(dataRow);
        }

        return dt;
    }

    /// <summary>
    ///     生成订单信息Excel
    /// </summary>
    /// <param name="templatePath">模版位置</param>
    /// <param name="list">集合</param>
    /// <returns></returns>
    public static async Task<XSSFWorkbook> GenerateExcelAsync<T>(string templatePath, List<T> list)
    {
        #region 读取模板文件

        await using var fileStream = File.OpenRead(templatePath);
        var workbook = new XSSFWorkbook(fileStream);
        var sheet = workbook.GetSheetAt(0);

        #endregion

        #region 模版数据行

        var lastRowNum = sheet.LastRowNum;
        var row = sheet.GetRow(lastRowNum);
        var rowHeight = row.Height;
        // 表格总行数
        var totalRowNum = lastRowNum;
        var colNum = row.LastCellNum;
        // 合并单元格
        var mergedRegions = new List<CellRangeAddress>();

        #endregion

        #region 填充模版数据

        foreach (var t in list)
        {
            // 子集合占用的行
            var newAddRowNum = 0;
            // 添加数据起始行
            totalRowNum += 1;
            var newRow = sheet.CreateRow(totalRowNum);
            newRow.Height = rowHeight;
            // 填充数据
            for (var j = 0; j < colNum; j++)
            {
                // 获取模版中当前单元格
                var cell = row.GetCell(j);
                if (cell is null) continue;
                // 设置单元格样式
                var newCell = newRow.CreateCell(j);
                newCell.CellStyle = cell.CellStyle;
                // 查询模版数值
                var templateCell =
                    TemplateCellOccupyRegex().Replace(cell.StringCellValue, string.Empty).Split(".")[1..];
                // 判断当前模版数量是否大于1
                if (templateCell.Length <= 1)
                {
                    var property = t!.GetType().GetProperty(templateCell[0]);
                    if (property is null) continue;
                    var propertyValue = GetPropertyValue(property, t);
                    newCell.SetCellValue(propertyValue);
                }
                // 超过三层模版不解析
                else if (templateCell.Length == 2)
                {
                    // 集合对象
                    if (templateCell[0].Contains("[]"))
                    {
                        var property = t!.GetType().GetProperty(templateCell[0].Replace("[]", ""));
                        if (property is null) continue;

                        var childrenList = property.GetValue(t) as IList;
                        newAddRowNum = childrenList?.Count - 1 ?? 0;
                        for (var z = 0; z < childrenList?.Count; z++)
                        {
                            var childrenProperty = childrenList[z]?.GetType().GetProperty(templateCell[1]);
                            var lastTemplateCellValue = childrenProperty is not null
                                ? GetPropertyValue(childrenProperty, childrenList[z]!)
                                : "";
                            if (z > 0)
                            {
                                // 添加数据起始行
                                var childrenNewRow = sheet.GetRow(totalRowNum + z) ??
                                                     sheet.CreateRow(totalRowNum + z);
                                childrenNewRow.Height = rowHeight;

                                var childrenNewCell = childrenNewRow.CreateCell(j);
                                childrenNewCell.CellStyle = cell.CellStyle;
                                childrenNewCell.SetCellValue(lastTemplateCellValue);
                            }
                            else newCell.SetCellValue(lastTemplateCellValue);
                        }
                    }
                    else
                    {
                        // 子对象嵌套类型
                        var property = t?.GetType().GetProperty(templateCell[0]);
                        if (property is null) break;

                        var childrenEntity = property.GetValue(t);
                        var childrenProperty = childrenEntity?.GetType().GetProperty(templateCell[1]);
                        if (childrenProperty is null) continue;

                        var lastTemplateCellValue = GetPropertyValue(property, childrenEntity!);
                        newCell.SetCellValue(lastTemplateCellValue);
                    }
                }
            }

            // 对刚才新添加的行单元格合并
            if (newAddRowNum <= 0) continue;
            for (var j = 0; j < colNum; j++)
            {
                var templateCell = row.GetCell(j);
                if (templateCell.StringCellValue.Contains("[]")) continue;

                for (var z = 0; z <= newAddRowNum; z++)
                {
                    var thisRow = sheet.GetRow(totalRowNum + z);
                    var thisCell = thisRow.GetCell(j);
                    if (thisCell is not null) continue;

                    thisCell = thisRow.CreateCell(j);
                    thisCell.CellStyle = templateCell.CellStyle;
                }

                mergedRegions.Add(new CellRangeAddress(totalRowNum - 1, totalRowNum - 1 + newAddRowNum, j, j));
            }

            // 更新总行数
            totalRowNum += newAddRowNum;
        }

        #endregion

        #region 删除模版行并更新表格

        sheet.RemoveRow(row);
        sheet.ShiftRows(lastRowNum + 1, sheet.LastRowNum, -1, true, false);

        #endregion

        #region 合并单元格

        // 合并单元格一定要放在最后，否则在清除了模版行后执行了ShiftRows方法后，合并单元格的区域会清空
        foreach (var mergedRegion in mergedRegions) sheet.AddMergedRegion(mergedRegion);

        #endregion

        return workbook;
    }

    /// <summary>
    ///     获取属性值
    /// </summary>
    /// <param name="property">属性</param>
    /// <param name="entity">实体</param>
    /// <returns></returns>
    private static dynamic GetPropertyValue(PropertyInfo property, object entity)
    {
        // 数组
        if (property.PropertyType.IsArray)
        {
            var value = property.GetValue(entity);
            return value is not null ? JsonSerializer.Serialize(value) : "";
        }

        // 枚举
        if (property.PropertyType.IsEnum)
        {
            return property.GetValue(entity) is System.Enum value ? EnumHelper.GetDescription(value) : "";
        }

        // 布尔
        if (property.PropertyType == typeof(bool) || property.PropertyType == typeof(bool?))
        {
            return property.GetValue(entity) is bool value ? value ? "是" : "否" : "";
        }

        // 时间
        if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
        {
            var value = property.GetValue(entity);
            return value is not null ? (DateTime)value : "";
        }

        // 值类型
        if (property.PropertyType == typeof(short) ||
            property.PropertyType == typeof(short?) ||
            property.PropertyType == typeof(int) ||
            property.PropertyType == typeof(int?) ||
            property.PropertyType == typeof(float) ||
            property.PropertyType == typeof(float?) ||
            property.PropertyType == typeof(double) ||
            property.PropertyType == typeof(double?) ||
            property.PropertyType == typeof(decimal) ||
            property.PropertyType == typeof(decimal?))
        {
            var value = property.GetValue(entity);
            return value is not null ? Convert.ToDouble(value) : "";
        }

        // 字符串类型
        if (property.PropertyType != typeof(string)) return "";
        {
            var value = property.GetValue(entity);
            return value is not null ? value.ToString()! : "";
        }
    }

    /// <summary>
    ///     判断是否为集合
    /// </summary>
    /// <param name="type">类型</param>
    /// <returns></returns>
    public static bool IsListOrEnumerableType(Type type)
    {
        // 排除字符串
        if (type == typeof(string)) return false;
        return typeof(IList).IsAssignableFrom(type) || typeof(IEnumerable).IsAssignableFrom(type);
    }
}